package com.example.spring.mapper;

import com.example.spring.entity.Good;
import com.example.spring.entity.Slot;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface SlotMapper {
    @Select("SELECT * FROM slot")
    List<Slot> findAll();

    @Insert("INSERT INTO slot(whid, trackNo, totalNo, currentNo) VALUES(#{whid}," +
            "#{trackNo}, #{totalNo}, #{currentNo})")
    int insert_data(Slot slot);

    int update(Slot slot);

    @Delete("DELETE FROM slot WHERE sid = #{sid}")
    Integer deleteById(@Param("sid") Integer sid);

    //指定whid，查每个货架类型的有多少个商品
    @Select("SELECT COUNT(*) FROM good AS g, slot AS s " +
            "WHERE g.sid = s.sid AND s.whid = #{whid} AND s.sid != 0 GROUP BY s.sid ORDER BY s.sid")
    List<Integer> findByWhidSlot(@Param("whid") Integer whid);

    //指定whid，查询货架
    @Select("SELECT sid FROM slot WHERE whid = #{whid} AND sid != 0 ORDER BY sid")
    List<Integer> findWhSlot(@Param("whid") Integer whid);

    //找slotId里的所有货物
    @Select("SELECT * FROM good WHERE sid = #{sid} AND sid != 0 ORDER BY sid LIMIT #{pageNum}, #{pageSize}")
    List<Good> findBySlotId(@Param("sid") Integer sid,
                            @Param("pageNum") Integer pageNum,
                            @Param("pageSize")Integer pageSize);
    //找slotId所有货物的总数
    @Select("SELECT COUNT(*) FROM good WHERE sid=#{sid}")
    Integer findBySlotId_Total(@Param("sid") Integer sid);
}
